
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 05/29/2013 16:21:51
-- Generated from EDMX file: D:\My Documents\Visual Studio 2010\Projects\MyProj.MVCProj\MyProj.MVCProj3.Model\Model.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [MyProj.MVCProj3];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------


-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------


-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'UserInfo'
CREATE TABLE [dbo].[UserInfo] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [UserName] nvarchar(32)  NOT NULL,
    [DelFlag] smallint  NOT NULL,
    [SubTime] datetime  NOT NULL,
    [Gender] smallint  NOT NULL,
    [Age] smallint  NOT NULL,
    [NickName] nvarchar(32)  NULL,
    [Email] nvarchar(128)  NOT NULL,
    [Phone] nvarchar(16)  NOT NULL,
    [Address] nvarchar(128)  NULL,
    [LastLoginDate] datetime  NOT NULL,
    [LastLoginIp] nvarchar(32)  NULL,
    [ErrorTime] int  NOT NULL,
    [UPwd] nvarchar(32)  NOT NULL
);
GO

-- Creating table 'Product'
CREATE TABLE [dbo].[Product] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [ProName] nvarchar(64)  NOT NULL,
    [BeforShortName] nvarchar(32)  NOT NULL,
    [AfterShortName] nvarchar(32)  NOT NULL,
    [ProductCode] nvarchar(max)  NOT NULL,
    [OnShelfDate] datetime  NOT NULL,
    [OffLineDate] datetime  NOT NULL,
    [DelFlag] smallint  NOT NULL,
    [ProductStatus] smallint  NOT NULL,
    [Remark] nvarchar(256)  NULL,
    [Description] nvarchar(max)  NULL,
    [SubTime] datetime  NOT NULL
);
GO

-- Creating table 'Role'
CREATE TABLE [dbo].[Role] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [RoleName] nvarchar(32)  NOT NULL,
    [DelFlag] smallint  NOT NULL,
    [RoleType] smallint  NOT NULL,
    [SubTime] datetime  NOT NULL,
    [Remark] nvarchar(256)  NULL
);
GO

-- Creating table 'ActionInfo'
CREATE TABLE [dbo].[ActionInfo] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [ActionInfoName] nvarchar(32)  NOT NULL,
    [Url] varchar(256)  NOT NULL,
    [HttpMethod] smallint  NOT NULL,
    [Remark] nvarchar(256)  NOT NULL,
    [DelFalg] smallint  NOT NULL,
    [SubTime] datetime  NOT NULL,
    [IsMenu] bit  NOT NULL
);
GO

-- Creating table 'R_UserInfo_ActionInfo'
CREATE TABLE [dbo].[R_UserInfo_ActionInfo] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [IsPass] smallint  NOT NULL,
    [UserInfoID] int  NOT NULL,
    [ActionInfoID] int  NOT NULL
);
GO

-- Creating table 'Department'
CREATE TABLE [dbo].[Department] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [DepName] nvarchar(32)  NOT NULL
);
GO

-- Creating table 'ActionGroup'
CREATE TABLE [dbo].[ActionGroup] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [GroupName] nvarchar(32)  NOT NULL,
    [GroupType] smallint  NOT NULL,
    [DelFlag] nvarchar(max)  NOT NULL,
    [Sort] int  NOT NULL
);
GO

-- Creating table 'Property'
CREATE TABLE [dbo].[Property] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [PropertyName] nvarchar(32)  NOT NULL,
    [SubTime] datetime  NOT NULL,
    [DelFlag] smallint  NOT NULL,
    [Remark] nvarchar(64)  NULL
);
GO

-- Creating table 'PropertyOption'
CREATE TABLE [dbo].[PropertyOption] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [OptionName] nvarchar(32)  NOT NULL,
    [DelFlag] smallint  NOT NULL,
    [SubTime] datetime  NOT NULL,
    [PropertyID] int  NOT NULL
);
GO

-- Creating table 'Category'
CREATE TABLE [dbo].[Category] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [ParentID] int  NOT NULL,
    [CategoryName] nvarchar(32)  NOT NULL,
    [CategoryType] smallint  NOT NULL,
    [Level] int  NOT NULL,
    [TreePath] nvarchar(max)  NOT NULL,
    [IsLeaf] bit  NOT NULL,
    [DelFalg] smallint  NOT NULL,
    [SubTime] datetime  NOT NULL
);
GO

-- Creating table 'ProductSKU'
CREATE TABLE [dbo].[ProductSKU] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [SkuCode] nvarchar(128)  NOT NULL,
    [ProductID] int  NOT NULL
);
GO

-- Creating table 'SKUOption'
CREATE TABLE [dbo].[SKUOption] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [PropId] nvarchar(max)  NOT NULL,
    [OptionId] nvarchar(max)  NOT NULL,
    [ProductSKUID] int  NOT NULL
);
GO

-- Creating table 'UserInfoRole'
CREATE TABLE [dbo].[UserInfoRole] (
    [UserInfo_ID] int  NOT NULL,
    [Role_ID] int  NOT NULL
);
GO

-- Creating table 'RoleActionInfo'
CREATE TABLE [dbo].[RoleActionInfo] (
    [Role_ID] int  NOT NULL,
    [ActionInfo_ID] int  NOT NULL
);
GO

-- Creating table 'DepartmentRole'
CREATE TABLE [dbo].[DepartmentRole] (
    [Department_ID] int  NOT NULL,
    [Role_ID] int  NOT NULL
);
GO

-- Creating table 'RoleActionGroup'
CREATE TABLE [dbo].[RoleActionGroup] (
    [Role_ID] int  NOT NULL,
    [ActionGroup_ID] int  NOT NULL
);
GO

-- Creating table 'ActionGroupActionInfo'
CREATE TABLE [dbo].[ActionGroupActionInfo] (
    [ActionGroup_ID] int  NOT NULL,
    [ActionInfo_ID] int  NOT NULL
);
GO

-- Creating table 'ProductProperty'
CREATE TABLE [dbo].[ProductProperty] (
    [Product_ID] int  NOT NULL,
    [Property_ID] int  NOT NULL
);
GO

-- Creating table 'CategoryProduct'
CREATE TABLE [dbo].[CategoryProduct] (
    [Category_ID] int  NOT NULL,
    [Product_ID] int  NOT NULL
);
GO

-- Creating table 'PropertyCategory'
CREATE TABLE [dbo].[PropertyCategory] (
    [Property_ID] int  NOT NULL,
    [Category_ID] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [ID] in table 'UserInfo'
ALTER TABLE [dbo].[UserInfo]
ADD CONSTRAINT [PK_UserInfo]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Product'
ALTER TABLE [dbo].[Product]
ADD CONSTRAINT [PK_Product]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Role'
ALTER TABLE [dbo].[Role]
ADD CONSTRAINT [PK_Role]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'ActionInfo'
ALTER TABLE [dbo].[ActionInfo]
ADD CONSTRAINT [PK_ActionInfo]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'R_UserInfo_ActionInfo'
ALTER TABLE [dbo].[R_UserInfo_ActionInfo]
ADD CONSTRAINT [PK_R_UserInfo_ActionInfo]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Department'
ALTER TABLE [dbo].[Department]
ADD CONSTRAINT [PK_Department]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'ActionGroup'
ALTER TABLE [dbo].[ActionGroup]
ADD CONSTRAINT [PK_ActionGroup]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Property'
ALTER TABLE [dbo].[Property]
ADD CONSTRAINT [PK_Property]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'PropertyOption'
ALTER TABLE [dbo].[PropertyOption]
ADD CONSTRAINT [PK_PropertyOption]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Category'
ALTER TABLE [dbo].[Category]
ADD CONSTRAINT [PK_Category]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'ProductSKU'
ALTER TABLE [dbo].[ProductSKU]
ADD CONSTRAINT [PK_ProductSKU]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'SKUOption'
ALTER TABLE [dbo].[SKUOption]
ADD CONSTRAINT [PK_SKUOption]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [UserInfo_ID], [Role_ID] in table 'UserInfoRole'
ALTER TABLE [dbo].[UserInfoRole]
ADD CONSTRAINT [PK_UserInfoRole]
    PRIMARY KEY NONCLUSTERED ([UserInfo_ID], [Role_ID] ASC);
GO

-- Creating primary key on [Role_ID], [ActionInfo_ID] in table 'RoleActionInfo'
ALTER TABLE [dbo].[RoleActionInfo]
ADD CONSTRAINT [PK_RoleActionInfo]
    PRIMARY KEY NONCLUSTERED ([Role_ID], [ActionInfo_ID] ASC);
GO

-- Creating primary key on [Department_ID], [Role_ID] in table 'DepartmentRole'
ALTER TABLE [dbo].[DepartmentRole]
ADD CONSTRAINT [PK_DepartmentRole]
    PRIMARY KEY NONCLUSTERED ([Department_ID], [Role_ID] ASC);
GO

-- Creating primary key on [Role_ID], [ActionGroup_ID] in table 'RoleActionGroup'
ALTER TABLE [dbo].[RoleActionGroup]
ADD CONSTRAINT [PK_RoleActionGroup]
    PRIMARY KEY NONCLUSTERED ([Role_ID], [ActionGroup_ID] ASC);
GO

-- Creating primary key on [ActionGroup_ID], [ActionInfo_ID] in table 'ActionGroupActionInfo'
ALTER TABLE [dbo].[ActionGroupActionInfo]
ADD CONSTRAINT [PK_ActionGroupActionInfo]
    PRIMARY KEY NONCLUSTERED ([ActionGroup_ID], [ActionInfo_ID] ASC);
GO

-- Creating primary key on [Product_ID], [Property_ID] in table 'ProductProperty'
ALTER TABLE [dbo].[ProductProperty]
ADD CONSTRAINT [PK_ProductProperty]
    PRIMARY KEY NONCLUSTERED ([Product_ID], [Property_ID] ASC);
GO

-- Creating primary key on [Category_ID], [Product_ID] in table 'CategoryProduct'
ALTER TABLE [dbo].[CategoryProduct]
ADD CONSTRAINT [PK_CategoryProduct]
    PRIMARY KEY NONCLUSTERED ([Category_ID], [Product_ID] ASC);
GO

-- Creating primary key on [Property_ID], [Category_ID] in table 'PropertyCategory'
ALTER TABLE [dbo].[PropertyCategory]
ADD CONSTRAINT [PK_PropertyCategory]
    PRIMARY KEY NONCLUSTERED ([Property_ID], [Category_ID] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [UserInfo_ID] in table 'UserInfoRole'
ALTER TABLE [dbo].[UserInfoRole]
ADD CONSTRAINT [FK_UserInfoRole_UserInfo]
    FOREIGN KEY ([UserInfo_ID])
    REFERENCES [dbo].[UserInfo]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Role_ID] in table 'UserInfoRole'
ALTER TABLE [dbo].[UserInfoRole]
ADD CONSTRAINT [FK_UserInfoRole_Role]
    FOREIGN KEY ([Role_ID])
    REFERENCES [dbo].[Role]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserInfoRole_Role'
CREATE INDEX [IX_FK_UserInfoRole_Role]
ON [dbo].[UserInfoRole]
    ([Role_ID]);
GO

-- Creating foreign key on [Role_ID] in table 'RoleActionInfo'
ALTER TABLE [dbo].[RoleActionInfo]
ADD CONSTRAINT [FK_RoleActionInfo_Role]
    FOREIGN KEY ([Role_ID])
    REFERENCES [dbo].[Role]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [ActionInfo_ID] in table 'RoleActionInfo'
ALTER TABLE [dbo].[RoleActionInfo]
ADD CONSTRAINT [FK_RoleActionInfo_ActionInfo]
    FOREIGN KEY ([ActionInfo_ID])
    REFERENCES [dbo].[ActionInfo]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_RoleActionInfo_ActionInfo'
CREATE INDEX [IX_FK_RoleActionInfo_ActionInfo]
ON [dbo].[RoleActionInfo]
    ([ActionInfo_ID]);
GO

-- Creating foreign key on [UserInfoID] in table 'R_UserInfo_ActionInfo'
ALTER TABLE [dbo].[R_UserInfo_ActionInfo]
ADD CONSTRAINT [FK_UserInfoR_UserInfo_ActionInfo]
    FOREIGN KEY ([UserInfoID])
    REFERENCES [dbo].[UserInfo]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserInfoR_UserInfo_ActionInfo'
CREATE INDEX [IX_FK_UserInfoR_UserInfo_ActionInfo]
ON [dbo].[R_UserInfo_ActionInfo]
    ([UserInfoID]);
GO

-- Creating foreign key on [ActionInfoID] in table 'R_UserInfo_ActionInfo'
ALTER TABLE [dbo].[R_UserInfo_ActionInfo]
ADD CONSTRAINT [FK_ActionInfoR_UserInfo_ActionInfo]
    FOREIGN KEY ([ActionInfoID])
    REFERENCES [dbo].[ActionInfo]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ActionInfoR_UserInfo_ActionInfo'
CREATE INDEX [IX_FK_ActionInfoR_UserInfo_ActionInfo]
ON [dbo].[R_UserInfo_ActionInfo]
    ([ActionInfoID]);
GO

-- Creating foreign key on [Department_ID] in table 'DepartmentRole'
ALTER TABLE [dbo].[DepartmentRole]
ADD CONSTRAINT [FK_DepartmentRole_Department]
    FOREIGN KEY ([Department_ID])
    REFERENCES [dbo].[Department]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Role_ID] in table 'DepartmentRole'
ALTER TABLE [dbo].[DepartmentRole]
ADD CONSTRAINT [FK_DepartmentRole_Role]
    FOREIGN KEY ([Role_ID])
    REFERENCES [dbo].[Role]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_DepartmentRole_Role'
CREATE INDEX [IX_FK_DepartmentRole_Role]
ON [dbo].[DepartmentRole]
    ([Role_ID]);
GO

-- Creating foreign key on [Role_ID] in table 'RoleActionGroup'
ALTER TABLE [dbo].[RoleActionGroup]
ADD CONSTRAINT [FK_RoleActionGroup_Role]
    FOREIGN KEY ([Role_ID])
    REFERENCES [dbo].[Role]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [ActionGroup_ID] in table 'RoleActionGroup'
ALTER TABLE [dbo].[RoleActionGroup]
ADD CONSTRAINT [FK_RoleActionGroup_ActionGroup]
    FOREIGN KEY ([ActionGroup_ID])
    REFERENCES [dbo].[ActionGroup]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_RoleActionGroup_ActionGroup'
CREATE INDEX [IX_FK_RoleActionGroup_ActionGroup]
ON [dbo].[RoleActionGroup]
    ([ActionGroup_ID]);
GO

-- Creating foreign key on [ActionGroup_ID] in table 'ActionGroupActionInfo'
ALTER TABLE [dbo].[ActionGroupActionInfo]
ADD CONSTRAINT [FK_ActionGroupActionInfo_ActionGroup]
    FOREIGN KEY ([ActionGroup_ID])
    REFERENCES [dbo].[ActionGroup]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [ActionInfo_ID] in table 'ActionGroupActionInfo'
ALTER TABLE [dbo].[ActionGroupActionInfo]
ADD CONSTRAINT [FK_ActionGroupActionInfo_ActionInfo]
    FOREIGN KEY ([ActionInfo_ID])
    REFERENCES [dbo].[ActionInfo]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ActionGroupActionInfo_ActionInfo'
CREATE INDEX [IX_FK_ActionGroupActionInfo_ActionInfo]
ON [dbo].[ActionGroupActionInfo]
    ([ActionInfo_ID]);
GO

-- Creating foreign key on [PropertyID] in table 'PropertyOption'
ALTER TABLE [dbo].[PropertyOption]
ADD CONSTRAINT [FK_PropertyPropertyOption]
    FOREIGN KEY ([PropertyID])
    REFERENCES [dbo].[Property]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PropertyPropertyOption'
CREATE INDEX [IX_FK_PropertyPropertyOption]
ON [dbo].[PropertyOption]
    ([PropertyID]);
GO

-- Creating foreign key on [Product_ID] in table 'ProductProperty'
ALTER TABLE [dbo].[ProductProperty]
ADD CONSTRAINT [FK_ProductProperty_Product]
    FOREIGN KEY ([Product_ID])
    REFERENCES [dbo].[Product]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Property_ID] in table 'ProductProperty'
ALTER TABLE [dbo].[ProductProperty]
ADD CONSTRAINT [FK_ProductProperty_Property]
    FOREIGN KEY ([Property_ID])
    REFERENCES [dbo].[Property]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProductProperty_Property'
CREATE INDEX [IX_FK_ProductProperty_Property]
ON [dbo].[ProductProperty]
    ([Property_ID]);
GO

-- Creating foreign key on [Category_ID] in table 'CategoryProduct'
ALTER TABLE [dbo].[CategoryProduct]
ADD CONSTRAINT [FK_CategoryProduct_Category]
    FOREIGN KEY ([Category_ID])
    REFERENCES [dbo].[Category]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Product_ID] in table 'CategoryProduct'
ALTER TABLE [dbo].[CategoryProduct]
ADD CONSTRAINT [FK_CategoryProduct_Product]
    FOREIGN KEY ([Product_ID])
    REFERENCES [dbo].[Product]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CategoryProduct_Product'
CREATE INDEX [IX_FK_CategoryProduct_Product]
ON [dbo].[CategoryProduct]
    ([Product_ID]);
GO

-- Creating foreign key on [Property_ID] in table 'PropertyCategory'
ALTER TABLE [dbo].[PropertyCategory]
ADD CONSTRAINT [FK_PropertyCategory_Property]
    FOREIGN KEY ([Property_ID])
    REFERENCES [dbo].[Property]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Category_ID] in table 'PropertyCategory'
ALTER TABLE [dbo].[PropertyCategory]
ADD CONSTRAINT [FK_PropertyCategory_Category]
    FOREIGN KEY ([Category_ID])
    REFERENCES [dbo].[Category]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PropertyCategory_Category'
CREATE INDEX [IX_FK_PropertyCategory_Category]
ON [dbo].[PropertyCategory]
    ([Category_ID]);
GO

-- Creating foreign key on [ProductID] in table 'ProductSKU'
ALTER TABLE [dbo].[ProductSKU]
ADD CONSTRAINT [FK_ProductProductSKU]
    FOREIGN KEY ([ProductID])
    REFERENCES [dbo].[Product]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProductProductSKU'
CREATE INDEX [IX_FK_ProductProductSKU]
ON [dbo].[ProductSKU]
    ([ProductID]);
GO

-- Creating foreign key on [ProductSKUID] in table 'SKUOption'
ALTER TABLE [dbo].[SKUOption]
ADD CONSTRAINT [FK_ProductSKUSKUOption]
    FOREIGN KEY ([ProductSKUID])
    REFERENCES [dbo].[ProductSKU]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProductSKUSKUOption'
CREATE INDEX [IX_FK_ProductSKUSKUOption]
ON [dbo].[SKUOption]
    ([ProductSKUID]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------